﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using Newtonsoft.Json;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Threading;
using UnityEngine;
/// <summary>
/// 实现从FBG服务器上获取的数据实时存入到数据库中
/// </summary>

public class FServerToMysql
{
    //创建一个MySqlAcces对象
    public static MySqlAcces SqlAcces;
    //创建一个NetLinkedAccess对象
    public Socket socketClient;//定义socket
    bool m_bThreadStop = false;
    #region
    public static Socket socketServer;//定义socket
    public static Thread threadWatch;//定义线程
    public static byte[] result = new byte[1024 * 1024 * 2];//定义缓存
    #endregion
    //查询数据库中是否存在某表的参数
    #region
    string tablesql = "SELECT * FROM information_schema.TABLES where table_name='fbg_test02' and TABLE_SCHEMA='db01';";
    #endregion
    //创建数据库的参数
    #region
    string tablename = "fbg_test02";
    string[] CloumName = new string[]
    {
        "ServiceNo","SensingNo","Time","波长1","波长2","波长3"
    };
    string[] cloumtype = new string[]
    {
        "varchar(255)","varchar(255)","datetime","varchar(255)","varchar(255)","varchar(255)"
    };
    #endregion
    //更新数据库的参数
    #region
    //string whereCloumsup = "''";

    //string valueup = "";

    #endregion
    //删除数据库的参数
    #region
    string[] whereCloumsde = { "''" };

    string[] valuede = { "''" };

    #endregion

    ///构造方法
    /// <summary>
    /// <param sqlAcces="_host">创建的MySqlAcces对象</param>
    /// </summary>
    public FServerToMysql(MySqlAcces Sql_Acces)
    {
        SqlAcces = Sql_Acces;
    }

    //读取从远程接受数据到数据库的方法
    public void FReadTCPToMysql(string serverip, int serverport)
    {
        //第一步：先建立与服务端的连接
        socketClient = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);//创建一个socket的对象
        IPAddress ip = IPAddress.Parse(serverip);//获取服务器IP地址
        IPEndPoint point = new IPEndPoint(ip, serverport);//获取端口
        try
        {
            socketClient.Connect(point);//链接服务器IP与端口
            Debug.Log("连接服务器中.....");
        }
        catch (Exception ex)
        {
            Debug.Log("与服务器链接失败！！！" + ex);
            return;
        }
        Debug.Log("与服务器链接成功！！！");
        //第二步：向服务器发送请求命令
        try
        {
            Thread.Sleep(1000);//等待1秒钟              
            SendMessages("scan");
        }
        catch
        {

        }
        //第三步：创建一个接受数据并将数据存入数据库的线程
        try
        {
            threadWatch = new Thread(FReceiveDataToMysql);//创建一个接受数据的线程
            threadWatch.IsBackground = true;//后台启动
            threadWatch.Start(socketClient);//运行
        }
        catch
        {

        }
    }

    private void FReceiveDataToMysql(object socketClient)
    {
        Socket socket = socketClient as Socket;//as执行类型的转换
        string temp = string.Empty;
        
        while (!m_bThreadStop)
        {
            int firstRcv = 0;//判断是否有数据
            byte[] buffer = new byte[1024 * 1024 * 5];//缓存定义
            try
            {
                //获取接受数据的长度，存入内存缓冲区，返回一个字节数组的长度
                if (socket != null)
                    firstRcv = socket.Receive(buffer);
                temp = Encoding.Default.GetString(buffer);
                Debug.Log(temp + firstRcv);
                if (firstRcv > 0)//大于0，说明有东西传过来
                {
                    SqlAcces.OpenSql();
                    //在MySqlAcces中已经创建了与数据库的连接
                    //先判断数据库中用于画图的表是否存在，如果存在，加入表中的数据；如果不存在，则创建表并加入数
                    //传来的数据是一个列表
                    if (SqlAcces.QuerySet(tablesql).Tables[0].Rows.Count > 0)
                    {
                        Debug.Log("表存在");
                        if (!string.IsNullOrEmpty(temp))
                        {
                            //传来的是一个json字符串
                            //利用JsonHelper类将json字符串转为数组
                            //将value和key分开即可
                            Debug.Log("222");
                            List<FBG_data> fBG_Datas = JsonHelper.DeserializeJsonToList<FBG_data>(temp);
                            Debug.Log("111");
                            //利用MySqlAcees类生成一条sql插入到数据库中
                            foreach (FBG_data fBGdata in fBG_Datas)
                            {                               
                                //获得value[]
                                string serviceno = fBGdata.ServiceNo;
                                string sensingNo = fBGdata.SensingNo;
                                string time = fBGdata.Time;
                                string bc1 = Convert.ToString(fBGdata.波长1);
                                string bc2 = Convert.ToString(fBGdata.波长2);
                                string bc3 = Convert.ToString(fBGdata.波长3);
                                string[] values = {serviceno, sensingNo,time,bc1,bc2,bc3};
                                SqlAcces.InsertInto(tablename,CloumName,values);
                            }                            
                        }
                    }
                    //如果不存在
                    else
                    {
                        Debug.Log($"表{tablename}不存在!");                      
                        if (!string.IsNullOrEmpty(temp))
                        {
                            //传来的是一个json字符串
                            //利用JsonHelper类将json字符串转为数组
                            //将value和key分开即可
                            string[] strs = JsonHelper.GetToJsonList(temp).ToObject<List<string>>().ToArray();
                            foreach (string i in strs)
                            {
                                //将json转为实体对象
                                FBG_data fBGdata = JsonHelper.DeserializeJsonToObject<FBG_data>(i);
                                //获得value[]
                                string serviceno = fBGdata.ServiceNo;
                                string sensingNo = fBGdata.SensingNo;
                                string time = fBGdata.Time;
                                string bc1 = Convert.ToString(fBGdata.波长1);
                                string bc2 = Convert.ToString(fBGdata.波长2);
                                string bc3 = Convert.ToString(fBGdata.波长3);
                                string[] values = {serviceno, sensingNo, time, bc1, bc2, bc3};
                                SqlAcces.InsertInto(tablename, CloumName, values);
                            }
                        }
                    }
                    SqlAcces.CloseSql();
                }
            }
            catch
            {

            }

        }
    }
    /// <summary>
    /// 发送消息到服务器的方法
    /// </summary>
    /// <param name="sendMessage">发送的内容</param>
    public void SendMessages(string sendMessage)//发送到服务端的内容
    {
        byte[] send = Encoding.UTF8.GetBytes(sendMessage);//Encoding.UTF8.GetBytes()将要发送的字符串转换成UTF8字节数组             
        socketClient.Send(send);  //将接受成功的消息返回给SocketServer服务器 
        Debug.Log("发送信息完毕：" + sendMessage);
    }
    //显示时间的函数
    public static DateTime GetTime()
    {
        DateTime now = new DateTime();
        now = DateTime.Now;
        return now;
    }
    //断开连接的方法
    public void SendExit()
    {
        if (threadWatch != null)
        {
            threadWatch.Abort();
        }
        if (socketClient != null)
        {
            m_bThreadStop = true;
            SendMessages("quit");
            socketClient.Shutdown(SocketShutdown.Both);
            socketClient.Close();
        }
    }
}

public class FBG_data
{
    /// <summary>
    /// 这是fbg数据对应的实体类
    /// </summary>
    public string ServiceNo
    {
        get; set;
    }
    /// <summary>
    /// 
    /// </summary>
    public string SensingNo
    {
        get; set;
    }
    /// <summary>
    /// 
    /// </summary>
    public string Time
    {
        get; set;
    }
    /// <summary>
    /// 
    /// </summary>
    public float 波长1
    {
        get; set;
    }
    /// <summary>
    /// 
    /// </summary>
    public float 波长2
    {
        get; set;
    }
    /// <summary>
    /// 
    /// </summary>
    public float 波长3
    {
        get; set;
    }
}

